Un petit coup de polish Nettoyage de fichiers Excel avec R

Rencontres R 2024

Thomas Vroylandt, Kantiles

Données et slides

https://github.com/tvroylandt/rr_2024

Qui suis-je ?

Thomas Vroylandt

Associé (Kantiles) thomas@kantiles.com

  • Rapports statistiques
  • Chaîne de production
  • Politiques sociales et de l’emploi

Cas réels, dans le cadre de production d’indicateurs pour un service statistique ministériel

Que demande le peuple ?

Source : Allison Horst

De l’autre côté du miroir

De l’autre côté du miroir

Une grande créativité dans les mises en forme des données !

Captures d’écrans qui se superpose de fichiers Excel en bordel avec smileys de réactions

Boîte à outils

  • readxl : import de fichiers rectangulaires simples

  • tidyxl : permet de transformer l’étape d’import en une étape de nettoyage de données

  • unpivotr : complément de tidyxl pour le nettoyage

  • A mentionner : openxlsx2 (sert aussi en écriture)

library(tidyverse)
library(readxl)
library(tidyxl)
library(unpivotr)

Insee - Taux d’activité

Population active et taux d’activité au sens du recensement selon le sexe et l’âge en 2020 : comparaisons départementales

path_insee_activite <- "data/TCRD_015.xlsx"

Insee - Taux d’activité

Intitulés des colonnes fusionnées

Insee - Taux d’activité

Zone des données décalée

Insee - Taux d’activité - readxl

En spécifiant les colonnes à la main

read_xlsx(
  path_insee_activite,
  sheet = "DEP",
  skip = 5,
  col_names = c(
    "code_dep",
    "lib_dep",
    "pop_active_tot",
    "pop_activite_h",
    "pop_active_f",
    "tx_act_15_64",
    "tx_act_25_54",
    "tx_act_h_15_64",
    "tx_act_h_25_54",
    "tx_act_f_15_64",
    "tx_act_f_25_54"
  )
)

Insee - Taux d’activité - readxl

Insee - Taux d’activité - tidyxl

xlsx_cells donne un tibble de la localisation et du contenu des cellules

xlsx_cells(path_insee_activite)

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3)

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var")

up-left car une cellule fusionnée ne remplit que le haut à gauche

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var")

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep")

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep")

Insee - Taux d’activité - tidyxl + unpivotr

xlsx_cells(path_insee_activite) |>
  filter(sheet == "DEP" & row >= 3) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep") |>
  select(typ_var, mod_var, code_dep, lib_dep, numeric)

Drees - ISD C24

Répartition des naissances par âge de la mère

path_c24 <- "data/C24-ISD_Part_accouchements_selon_age_mere.xlsx"

Drees - ISD C24

Début des données décalées

Drees - ISD C24

Intitulés des colonnes fusionnées

Drees - ISD C24

Un onglet par année (mais tous pareil) + un onglet de documentation

Drees - ISD C24

Des données à la fin que l’on ne souhaite pas importer

Drees - ISD C24 - readxl

On prend les colonnes telles quelles

read_xlsx(path_c24,
          sheet = "2022",
          skip = 5,
          .name_repair = "unique_quiet") |>
  rename(code_dep = ...1,
         lib_dep = ...2) |>
  pivot_longer(-c(code_dep, lib_dep),
               names_to = "age_mere",
               values_to = "perc_naiss")

Drees - ISD C24 - readxl

Idem sur les onglets dans un map

excel_sheets(path_c24) |>
  set_names() |>
  _[-12] |>
  map(read_xlsx,
      path = path_c24,
      skip = 5,
      .name_repair = "unique_quiet") |>
  list_rbind(names_to = "annee") |>
  rename(code_dep = ...1,
         lib_dep = ...2) |>
  pivot_longer(-c(annee, code_dep, lib_dep),
               names_to = "age_mere",
               values_to = "perc_naiss") |>
  mutate(annee = as.numeric(annee)) |>
  filter(!is.na(code_dep) &
           !is.na(perc_naiss) & 
           !code_dep %in% c("F", "M"))

Drees - ISD C24 - readxl

Drees - ISD C24 - tidyxl

df_cells_c24 <- xlsx_cells(path_c24)

df_cells_c24

Drees - ISD C24 - tidyxl + dplyr/tidyr

df_cells_c24 |>
  filter(sheet != "Documentation") |>
  filter(row >= 4 & !is_blank & col != 2) |>
  select(sheet, row, col, character, numeric) |>
  mutate(header_row = case_when(row == 6 ~ character),
         code_dep = case_when(col == 1 ~ coalesce(as.character(numeric), character))) |>
  group_by(col) |>
  fill(header_row, .direction = "down") |>
  group_by(row) |>
  fill(code_dep, .direction = "down") |>
  ungroup() |>
  filter(!is.na(numeric) &
           !is.na(code_dep) &
           !is.na(header_row) & !code_dep %in% c("F", "M")) |>
  mutate(sheet = as.numeric(sheet),
         header_row = str_trim(header_row)) |>
  select(annee = sheet,
         code_dep,
         age_mere = header_row,
         perc_naiss = numeric)

Drees - ISD C24 - tidyxl + unpivotr

penser à grouper par onglet

df_cells_c24 |>
  filter(row >= 5) |>
  group_by(sheet) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep") |>
  ungroup() |>
  mutate(mod_var = str_trim(mod_var)) |>
  select(sheet, typ_var, mod_var, code_dep, lib_dep, numeric) |>
  filter(!is.na(code_dep) &
           !code_dep %in% c("F", "M") &
           !is.na(numeric)) 

Drees - ISD C24 - tidyxl + unpivotr

Résultat, avec Observable

Quelques astuces

  • on peut paramétriser le nombre de lignes à sauter par onglet (dans le filter), car cela arrive que les espacements ne soient pas les mêmes d’une année sur l’autre
  • penser à utiliser les fonctions de dplyr et tidyr comme fill ou coalesce en complément
  • séparer par bloc + faire des str_detect

exemple pour récupérer les dates de MAJ de chaque onglet

df_cells_c24 |>
  select(sheet, character) |>
  filter(str_detect(character, "mise à jour")) |>
  mutate(
    date_maj = str_remove(character, "Date de mise à jour : "),
    date_maj = dmy(date_maj)
  )

FAJ - Dernier exemple sur le sujet

Cela fonctionne dans la plupart des cas assez bien, même avec des cellules fusionnées et d’autres non

petite subtilité car tous les onglets ne commencent pas au même endroit et ne terminent pas au même endroit

df_cells_faj <- xlsx_cells("data/FAJ Données annuelles 2007-2022.xlsx") |>
  # on filtre sur les onglets qui nous intéressent - on retire FAJ2015 qui a une structuration différente et qu'il faudrait traiter à part
  filter(str_sub(sheet, 1, 6) == "FAJ 20" & sheet != "FAJ 2015")

# ligne de début
df_cells_faj_min <- df_cells_faj |> 
  filter(character == "Départements") |> 
  select(sheet, row_min = row)

# ligne de fin - on aurait aussi pu détecter La Réunion
df_cells_faj_max <- df_cells_faj |> 
  filter(is_blank & col == 1) |> 
  inner_join(df_cells_faj_min, by = join_by(sheet)) |> 
  # on ajoute quelques lignes
  filter(row > row_min + 3) |> 
  group_by(sheet) |> 
  filter(row == min(row)) |>
  ungroup() |> 
  select(sheet, row_max = row) |>
  # comme on a detecté la ligne blanche suivant la fin, on décale
  mutate(row_max = row_max - 1)

# on jointe pour cibler les tableaux
df_cells_faj |>
  inner_join(df_cells_faj_min, by = join_by(sheet)) |>
  inner_join(df_cells_faj_max, by = join_by(sheet)) |>
  filter(between(row, row_min, row_max)) |>
  group_by(sheet) |>
  behead("up-left", "typ_var") |>
  behead("up", "mod_var") |>
  behead("left", "code_dep") |>
  behead("left", "lib_dep") |>
  ungroup() |>
  select(sheet, typ_var, mod_var, code_dep, lib_dep, character, numeric)

En v2 FAJ - partitions

Tab 2022 avec deux tabs dans le même –> séparation des tabs plutôt que de ne faire que le premier

on va partitionner les cellules en tableaux, comme un nest, puis on va pouvoir soit travailler indépendemment, soit avec un map si la structure est similaire travailler le sujet

pour cela, il va falloir délimiter, ici on va le faire par les en-têtes, mais cela peut aussi se faire avec une détection des blancs (mais plus complexe avec les cellules fusionnées)

df_cells_faj_filtered <- df_cells_faj |>
  filter(sheet == "FAJ 2022" & row >= 9)

df_title_cells_faj <- df_cells_faj_filtered |>
  filter(character %in% c("Départements", "Métropoles et départements hors métropoles"))

# on partitionne
partitions_faj <- partition(df_cells_faj_filtered,
          df_title_cells_faj)

# on reprendre le code du dessus
# et on assemble
map(
  partitions_faj$cells,
  \(data) data |>
    behead("up-left", "typ_var") |>
    behead("up", "mod_var") |>
    behead("left", "code_dep") |>
    behead("left", "lib_dep") |>
    select(typ_var, mod_var, code_dep, lib_dep, character, numeric)
) |>
  set_names("dep", "metro") |>
  list_rbind(names_to = "type_geo")

Panorama statistique

démo pour le format sous une autre forme que le texte (gras, couleur)

On va s’attacher à un seul tableau

Panorama statistique

path_panorama <- "data/PanoFrance2022.xlsx"

# chargement des cellules + délimitation
df_cells_panorama <- xlsx_cells(path_panorama) |> 
  filter(between(row, 330, 355))

# et du format - liste
format_panorama <- xlsx_formats(path_panorama)

df_panorama_cleaned <- df_cells_panorama |>
  # il faudra gérer la fusion des codes géographiques à part
  behead("up", "code_geo") |>
  # type d'établissement en gras - penser à indexer sur le type d'établissement
  behead_if(format_panorama$local$font$bold[local_format_id],
            direction = "left-up",
            name =  "type_etab") |>
  behead_if(
    format_panorama$local$alignment$indent[local_format_id] == 2,
    direction = "left-up",
    name =  "type_var"
  ) |>
  behead_if(
    format_panorama$local$alignment$indent[local_format_id] == 4,
    direction = "left",
    name =  "type_places"
  ) |>
  select(code_geo, type_etab, type_var, type_places, numeric, character) |> 
  filter(!is.na(numeric))

df_panorama_cleaned

Quelques astuces

  • ne pas hésiter à coder à la main des choses, tout ne peut pas être automatisé (enfin, tout peut l’être, mais il faut doser le coût-bénéfice)
  • de façon générale, une formattage = un code
  • faire plusieurs passes, par exemple pour se faire un fichier de référence des noms de colonnes utilisables derrière
  • ce n’est que de la manip de données
  • on peut aussi faire sur plusieurs fichiers Excel à la fois si ils sont formattés pareil
  • privilégier quand c’est possible d’autres exports (c’est souvent le cas notamment en open data avec le paramétrage OpenDataSoft qui force + parquet)

Questions ?

Production statistique

Rapports statistiques

Politiques sociales et de l’emploi

Handicap

Caractéristiques

Récupération des données

Calcul des indicateurs

Mise en forme des fichiers

Validation

Documentation

Mise en ligne